import pymysql
import pymongo
import time, datetime

def get_comment():
    goodstable = 'goodsrank' #设置数据表名字
    client = pymongo.MongoClient('localhost',27017)
    dataname = client['goods']
    table = dataname[goodstable]
    return table.find()
def import_comment():
    # 建立数据库连接
    conn=pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='root',
        db='dscmall',
        charset='utf8mb4',
    )
    # 获取游标
    cursor=conn.cursor(pymysql.cursors.DictCursor)
    comments = get_comment()
    data = []
    for c in comments:
        # 转为时间数组
        timeArray = time.strptime(c['date'], "%Y年%m月%d日 %H:%M")
        timestamp = int(time.mktime(timeArray))
        comment_type = 0,
        id_value = c['goods_id'],
        email = '',
        user_name = c['title'],
        content = c['content'],
        comment_rank = 4,
        comment_server = 5,
        comment_delivery = 5,
        add_time = timestamp,
        ip_address = '127.0.0.1',
        status = 1,
        parent_id = 0,
        user_id = 3084,
        ru_id = 3065,
        single_id = 0,
        order_id = 453,
        rec_id = 371,
        goods_tag = '',
        useful = 0,
        useful_user = '',
        use_ip = 0,
        dis_id = 0,
        like_num = 0,
        dis_browse_num = 0
        info = (comment_type, id_value, email, user_name, content, comment_rank, comment_server, comment_delivery, add_time, ip_address, status, parent_id, user_id, ru_id, single_id, order_id, rec_id, goods_tag, useful, useful_user, use_ip, dis_id, like_num, dis_browse_num)
        data.append(info)
    sql = 'insert into dsc_comment(comment_type, id_value, email, user_name, content, comment_rank, comment_server, comment_delivery, add_time, ip_address, status, parent_id, user_id, ru_id, single_id, order_id, rec_id, goods_tag, useful, useful_user, use_ip, dis_id, like_num, dis_browse_num) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
    # 执行sql语句
    for d in data:
        cursor.execute(sql,d)
        conn.commit()
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()
    print('评论数据导入完成')
# 数据去重-关键字替换
def handle_data():
    goodstable = 'goodsrank' #设置数据表名字
    client = pymongo.MongoClient('localhost',27017)
    dataname = client['goods']
    table = dataname[goodstable]
    i = 0
    for item in table.find():
        repeating = table.find_one({'title': item['title'],'goods_id':item['goods_id']})
        if repeating['content'].find('淘宝') != -1:
            repeating['content'] = repeating['content'].replace('淘宝','东西协作平台')
        if repeating['content'].find('天猫') != -1:
            repeating['content'] = repeating['content'].replace('天猫','')
        if repeating['content'].find('淘气') != -1:
            repeating['content'] = repeating['content'].replace('淘气','积分')
        # # 删除所有id相同的数据
        result = table.delete_many({'title': item['title'],'goods_id':item['goods_id']})
        # 把刚刚复制的数据加入一条到数据库
        table.insert_one(repeating)
    print('评论数据处理完成')
# 查询不存在评论的商品
def get_goods():
    # 建立数据库连接
    conn=pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='root',
        db='dscmall',
        charset='utf8mb4',
    )
    # 获取游标
    cursor=conn.cursor(pymysql.cursors.DictCursor)
    sql = 'SELECT id_value FROM dsc_comment'
    # 执行sql语句
    cursor.execute(sql)
    goods_comment = cursor.fetchall()

    sql2 = 'SELECT goods_id,goods_name FROM dsc_goods'
    cursor.execute(sql2)
    goods = cursor.fetchall()

    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()

    goodstable = 'goods' #设置数据表名字
    client = pymongo.MongoClient('localhost',27017)
    dataname = client['goods']
    table = dataname[goodstable]

    for g in goods:
        flag = False
        for c in goods_comment:
            if c['id_value'] == g['goods_id']:
                flag = True
        if flag == False:
            print(g['goods_id'])
            table.insert_one(g)

    
if __name__ == "__main__":
    get_goods()
    # handle_data()
    # import_comment()